package com.escargear39.util;

import java.util.HashMap;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

    private final String TABLE_FRIENDS = "FRIENDS";

    private final String FRIENDS_ID = "ID";
    private final String FRIENDS_FAMILY_NAME = "FAMILY_NAME";
    private final String FRIENDS_FAMILY_NAME_READING = "FAMILY_NAME_READING";
    private final String FRIENDS_FIRST_NAME = "FIRST_NAME";
    private final String FRIENDS_FIRST_NAME_READING = "FIRST_NAME_READING";
    private final String FRIENDS_PICTURE = "PICTURE";
    private final String FRIENDS_NICKNAME = "NICKNAME";
    private final String FRIENDS_COUNTRY_OF_ORIGIN = "COUNTRY_OF_ORIGIN";
    private final String FRIENDS_BIRTHDAY = "BIRTHDAY";
    private final String FRIENDS_ADDRESS = "ADDRESS";
    private final String FRIENDS_PHONE_NUMBER = "PHONE_NUMBER";
    private final String FRIENDS_MAIL_ADDRESS1 = "MAIL_ADDRESS1";
    private final String FRIENDS_MAIL_ADDRESS2 = "MAIL_ADDRESS2";
    private final String FRIENDS_ORGANIZATION = "ORGANIZATION";
    private final String FRIENDS_MET_DATE = "MET_DATE";
    private final String FRIENDS_MET_LOCATION = "MET_LOCATION";
    private final String FRIENDS_CREATE_DATE = "CREATE_DATE";
    private final String FRIENDS_UPDATE_DATE = "UPDATE_DATE";

    /** テーブルを作成するSQL */
    private final String CREATE_TABLE = "create table FRIENDS("
            + "     ID integer, FAMILY_NAME, FAMILY_NAME_READING, FIRST_NAME, "
            + "     FIRST_NAME_READING, PICTURE, NICKNAME, COUNTRY_OF_ORIGIN, "
            + "     BIRTHDAY, ADDRESS, PHONE_NUMBER, MAIL_ADDRESS1, "
            + "     MAIL_ADDRESS2, ORGANIZATION, MET_DATE, MET_LOCATION, CREATE_DATE, UPDATE_DATE)";

    /**
     * コンストラクタ
     *
     * @param context
     */
    public DatabaseHelper(Context context) {
        // ストレージ（ローカルファイル）にDBを作成
        super(context, "BookOfFriends.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            db.execSQL(CREATE_TABLE);
        } catch (SQLException e) {
            e.getStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
        // TODO 自動生成されたメソッド・スタブ

    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        // TODO 自動生成されたメソッド・スタブ
        super.onOpen(db);
    }

    /**
     * データ一覧を取得するメソッド
     *
     * @param db
     *            データベース
     * @param orderBy
     *            order by句に指定するカラム名
     * @return 一覧データ
     */
    public HashMap<Integer, HashMap<String, String>> getList(SQLiteDatabase db,
            String orderBy) {
        HashMap<Integer, HashMap<String, String>> map = new HashMap<Integer, HashMap<String, String>>();
        String[] columns = { FRIENDS_ID, FRIENDS_FAMILY_NAME,
                FRIENDS_FIRST_NAME, FRIENDS_PICTURE };
        // SQLの実行
        Cursor c = db.query(TABLE_FRIENDS, columns, null, null, null, null,
                orderBy);

        int count = 0;
        while (c.moveToNext()) {
            HashMap<String, String> personalInfo = new HashMap<String, String>();

            // 要素の取り出し
            personalInfo.put(FRIENDS_ID,
                    c.getString(c.getColumnIndex(FRIENDS_ID)));
            personalInfo.put(FRIENDS_FAMILY_NAME,
                    c.getString(c.getColumnIndex(FRIENDS_FAMILY_NAME)));
            personalInfo.put(FRIENDS_FIRST_NAME,
                    c.getString(c.getColumnIndex(FRIENDS_FIRST_NAME)));
            personalInfo.put(FRIENDS_PICTURE,
                    c.getString(c.getColumnIndex(FRIENDS_PICTURE)));

            // 戻り値につめる
            map.put(Integer.valueOf(count), personalInfo);
            count++;
        }

        // カーソルを閉じる
        c.close();

        return map;
    }

    /**
     * 個人データを取得するメソッド
     *
     * @param db
     *            データベース
     * @param id
     *            選択されたid
     * @return 個人データ
     */
    public HashMap<String, String> getPersonalInfo(SQLiteDatabase db, String id) {
        HashMap<String, String> map = new HashMap<String, String>();
        String[] columns = { FRIENDS_ID, FRIENDS_FAMILY_NAME,
                FRIENDS_FAMILY_NAME_READING, FRIENDS_FIRST_NAME,
                FRIENDS_FIRST_NAME_READING, FRIENDS_PICTURE, FRIENDS_NICKNAME,
                FRIENDS_COUNTRY_OF_ORIGIN, FRIENDS_BIRTHDAY, FRIENDS_ADDRESS,
                FRIENDS_PHONE_NUMBER, FRIENDS_MAIL_ADDRESS1,
                FRIENDS_MAIL_ADDRESS2, FRIENDS_ORGANIZATION, FRIENDS_MET_DATE,
                FRIENDS_MET_LOCATION, FRIENDS_CREATE_DATE, FRIENDS_UPDATE_DATE };

        // SQLの実行
        String where = "ID = '1'";
        //String[] where_args = { "1" };
        Cursor c = db.query(TABLE_FRIENDS, columns, where, null, null,
                null, null);
        while (c.moveToNext()) {
            // 要素の取り出し
            map.put(FRIENDS_FAMILY_NAME,
                    c.getString(c.getColumnIndex(FRIENDS_FAMILY_NAME)));
            map.put(FRIENDS_FAMILY_NAME_READING,
                    c.getString(c.getColumnIndex(FRIENDS_FAMILY_NAME_READING)));
            map.put(FRIENDS_FIRST_NAME,
                    c.getString(c.getColumnIndex(FRIENDS_FIRST_NAME)));
            map.put(FRIENDS_FIRST_NAME_READING,
                    c.getString(c.getColumnIndex(FRIENDS_FIRST_NAME_READING)));
            map.put(FRIENDS_PICTURE,
                    c.getString(c.getColumnIndex(FRIENDS_PICTURE)));
            map.put(FRIENDS_NICKNAME,
                    c.getString(c.getColumnIndex(FRIENDS_NICKNAME)));
            map.put(FRIENDS_COUNTRY_OF_ORIGIN,
                    c.getString(c.getColumnIndex(FRIENDS_COUNTRY_OF_ORIGIN)));
            map.put(FRIENDS_BIRTHDAY,
                    c.getString(c.getColumnIndex(FRIENDS_BIRTHDAY)));
            map.put(FRIENDS_ADDRESS,
                    c.getString(c.getColumnIndex(FRIENDS_ADDRESS)));
            map.put(FRIENDS_PHONE_NUMBER,
                    c.getString(c.getColumnIndex(FRIENDS_PHONE_NUMBER)));
            map.put(FRIENDS_MAIL_ADDRESS1,
                    c.getString(c.getColumnIndex(FRIENDS_MAIL_ADDRESS1)));
            map.put(FRIENDS_MAIL_ADDRESS2,
                    c.getString(c.getColumnIndex(FRIENDS_MAIL_ADDRESS2)));
            map.put(FRIENDS_ORGANIZATION,
                    c.getString(c.getColumnIndex(FRIENDS_ORGANIZATION)));
            map.put(FRIENDS_MET_DATE,
                    c.getString(c.getColumnIndex(FRIENDS_MET_DATE)));
            map.put(FRIENDS_MET_LOCATION,
                    c.getString(c.getColumnIndex(FRIENDS_MET_LOCATION)));
            map.put(FRIENDS_CREATE_DATE,
                    c.getString(c.getColumnIndex(FRIENDS_CREATE_DATE)));
            map.put(FRIENDS_UPDATE_DATE,
                    c.getString(c.getColumnIndex(FRIENDS_UPDATE_DATE)));
        }

        // カーソルを閉じる
        c.close();

        return map;
    }

}
